﻿using Sidvall.Serialization;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Threading.Tasks;

namespace Sidvall.Data
{
    public class SqlServerProvider : Sidvall.Data.SqlProvider
    {
        #region Public Members

        #region CreateDatabaseAsync

        public override async Task<bool> CreateDatabaseAsync(string connectionString, string database, SqlExceptionBehavior exceptionBehavior)
        {
            using (var cn = new SqlConnection(connectionString))
            {
                await cn.OpenAsync().ConfigureAwait(false);
                var commandText = @"SELECT COUNT(*) FROM sys.databases WHERE [Name] = '" + database + "'";
                var count = (long)(await DataManager.ExecuteScalarAsync(cn, exceptionBehavior, commandText).ConfigureAwait(false));
                if (count == 0)
                {
                    commandText = @"CREATE DATABASE [" + database + "]";
                    await DataManager.ExecuteNonQueryAsync(cn, exceptionBehavior, commandText).ConfigureAwait(false);
                    return true;
                }
            }
            return false;
        }

        #endregion
        #region ExecuteNonQueryAsync

        public override async Task<int> ExecuteNonQueryAsync(string connectionString, string sqlQuery, SqlExceptionBehavior exceptionBehavior)
        {
            var commandTexts = GetCommandTexts(sqlQuery);
            if (commandTexts.Count == 0)
                return 0;
            using (var cn = new SqlConnection(connectionString))
            {
                await cn.OpenAsync().ConfigureAwait(false);
                return await DataManager.ExecuteNonQueryAsync(cn, exceptionBehavior, commandTexts).ConfigureAwait(false);
            }
        }

        #endregion
        #region ExecuteScalarAsync

        public override async Task<object> ExecuteScalarAsync(string connectionString, string sqlQuery, SqlExceptionBehavior exceptionBehavior)
        {
            using (var cn = new SqlConnection(connectionString))
            {
                await cn.OpenAsync().ConfigureAwait(false);
                return await DataManager.ExecuteScalarAsync(cn, exceptionBehavior, sqlQuery).ConfigureAwait(false);
            }
        }

        #endregion
        #region GetTablesAsync

        public override async Task<TableItemCollection> GetTablesAsync(string connectionString, bool loadRowCount, SqlExceptionBehavior exceptionBehavior)
        {
            var items = new TableItemCollection();
            using (var cn = new SqlConnection(connectionString))
            {
                await cn.OpenAsync().ConfigureAwait(false);
                var commandText = @"SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'";
                using (var reader = await DataManager.ExecuteReaderAsync(cn, commandText).ConfigureAwait(false))
                {
                    while (await reader.ReadAsync().ConfigureAwait(false))
                    {
                        var name = await reader.GetFieldValueAsync<string>(0).ConfigureAwait(false);
                        items.Add(name, null);
                    }
                }
                if (loadRowCount)
                {
                    foreach (var item in items)
                    {
                        commandText = @"SELECT count(*) FROM [" + item.Name + "]";
                        var value = await DataManager.ExecuteScalarAsync(cn, exceptionBehavior, commandText).ConfigureAwait(false);
                        item.RowCount = value.ToNullableInt64();
                    }
                }
            }
            return items;
        }

        #endregion

        #endregion
        #region Private Members

        #region GetCommandTexts

        private List<string> GetCommandTexts(string sqlQuery)
        {
            var commandTexts = new List<string>();
            var sqlLines = sqlQuery.Split("\n", System.StringSplitOptions.RemoveEmptyEntries);
            var commandText = string.Empty;
            foreach (var sqlLine in sqlLines)
            {
                if (string.IsNullOrWhiteSpace(sqlLine))
                    continue;
                var line = sqlLine.Trim();
                if (line.StartsWith("/*", System.StringComparison.OrdinalIgnoreCase))
                    continue;
                if (line.Equals("go", System.StringComparison.OrdinalIgnoreCase))
                {
                    if (!string.IsNullOrEmpty(commandText))
                        commandTexts.Add(commandText);
                    commandText = string.Empty;
                    continue;
                }
                if (!string.IsNullOrWhiteSpace(commandText))
                    commandText += System.Environment.NewLine;
                commandText += line;
            }
            if (!string.IsNullOrEmpty(commandText))
                commandTexts.Add(commandText);
            return commandTexts;
        }

        #endregion

        #endregion
    }
}
